package com.boot.core.task


import com.boot.config.ParamConfig
import com.boot.core.task.report122.conn.MySqlDBConn
import com.boot.core.task.report122.conn.OracleDBConn
import com.boot.mgt.dao.mysql.AccidentFileMapper
import com.boot.mgt.model.AccidentFile
import com.boot.mgt.service.AccidentFileService
import com.boot.mgt.service.CurrentRowService
import com.google.common.collect.Sets
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.core.simple.SimpleJdbcInsert
import org.springframework.jdbc.datasource.DataSourceTransactionManager
import org.springframework.scheduling.annotation.Scheduled
import org.springframework.stereotype.Component
import org.springframework.transaction.TransactionStatus
import org.springframework.transaction.support.TransactionCallbackWithoutResult
import org.springframework.transaction.support.TransactionTemplate
import org.springframework.util.StringUtils
import java.sql.*
import java.time.format.DateTimeFormatter
import java.util.*


@Component
class TaskConvert {
    @Autowired
    private lateinit var currentRowService: CurrentRowService

    @Autowired
    private lateinit var accidentFileService: AccidentFileService

    @Autowired
    private lateinit var paramConfig: ParamConfig

    @Autowired
    private lateinit var accidentFileMapper: AccidentFileMapper

//    @Scheduled(fixedDelay = 1000 * 30)
    fun reportSyncData122() {
        val oracleDBConn = OracleDBConn()
        val oracleList: List<Any> = oracleDBConn.oracleSyncReportData()
        if (oracleList.isNotEmpty()) {
            val mySqlDBConn = MySqlDBConn()
            mySqlDBConn.mysqlOperateReport(oracleList)
        }
    }

//    @Scheduled(fixedDelay = 1000 * 60)
    fun tableInput(): List<Map<String, Any>>? {
        var lastGXSJ = accidentFileMapper.selectMaxGXSJ() ?: "1970-01-01 00:00:00.0"
        lastGXSJ = lastGXSJ.split(".")[0]
        val connection = getConnection()
        var resultSet: ResultSet? = null
        var sql = "SELECT * FROM TRFF_APP.ACD_FILE WHERE xzqh like '6101%' AND " +
                "TRFF_APP.ACD_FILE.gxsj > to_date('LAST_GXSJ','yyyy-mm-dd hh24:mi:ss')" +
                "ORDER BY TRFF_APP.ACD_FILE.gxsj ASC"
        sql = sql.replace("LAST_GXSJ", lastGXSJ)

        log.info("execute accident query {}", sql);

        try {
            var resultSet = connection?.prepareStatement(sql)?.executeQuery()
            var i = 0
            val columns = getColumns(resultSet!!)
            val records = ArrayList<HashMap<String, Any>>()
            while (resultSet.next()) {
                val record = HashMap<String, Any>()
                for (column in columns) {
                    val value = resultSet.getString(column)
                    if (StringUtils.isEmpty(value)) {
                        record[column] = ""
                        continue
                    }
                    record["latitude"] = "0.0"
                    record["longitude"] = "0.0"
                    record[column] = value
                }
                records.add(record)
                i++
                if (i % 1000 == 0) {
                    convertRecords(records)
                }
            }
            convertRecords(records)//提交剩余的

            return records
        } catch (e: SQLException) {
            // TODO Auto-generated catch block
            e.printStackTrace()
        } finally {

            try {
            } catch (e: SQLException) {
                e.printStackTrace()
            }

            try {
                connection!!.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }

        }
        connection?.close()
        return null
    }


    fun convertRecords(records: ArrayList<HashMap<String, Any>>): Int {
        if (records.isEmpty()) {
            return 0;
        }
        val insertRecords = ArrayList<HashMap<String, Any>>()
        val updateRecords = ArrayList<HashMap<String, Any>>()

        for (record in records) {
            val sgbh = record["sgbh"].toString()
            var sgbhAmount = accidentFileMapper.selectSGBHAmount(sgbh)
            if (sgbhAmount.equals(0)) {
                insertRecords.add(record)
            } else {
                updateRecords.add(record)
            }
        }

        log.info("accident_file->will insert {} records", insertRecords.size)
        log.info("accident_file->will update {} records", updateRecords.size)

        val dataSource = MysqlDataSource()
        dataSource.setUrl(paramConfig.mysqlSource.url)
        dataSource.user = paramConfig.mysqlSource.username
        dataSource.setPassword(paramConfig.mysqlSource.password)
        val transactionManager = DataSourceTransactionManager(dataSource)

        //insert
        TransactionTemplate(transactionManager).execute(object : TransactionCallbackWithoutResult() {
            override fun doInTransactionWithoutResult(status: TransactionStatus) {
                val insertTemplate = SimpleJdbcInsert(dataSource).withTableName("accident_file")

                for (record in insertRecords) {
                    record.put("kskcsj", formatterTime(record["kskcsj"].toString()))
                    record.put("jskcsj", formatterTime(record["jskcsj"].toString()))
                    record.put("sgfssj", formatterTime(record["sgfssj"].toString()))
                    //
                    val result = insertTemplate.execute(record)
                    log.debug("accident_file->insert new sgbh {} ", record.get("sgbh"));
                }
            }
        })
        //数据比较
        var accidentList = accidentFileService.compareAccident(insertRecords)
        if (accidentList.size > 0) {
            accidentFileService.editGpsTask(accidentList)
        }

        //update
        val deleteSql = "delete from accident_file where sgbh = ?"
        TransactionTemplate(transactionManager).execute(object : TransactionCallbackWithoutResult() {
            override fun doInTransactionWithoutResult(status: TransactionStatus) {
                var jdbcTemplate = JdbcTemplate(dataSource)
                var insertTemplate = SimpleJdbcInsert(dataSource).withTableName("accident_file")

                for (record in updateRecords) {
                    log.info("accident_file->delete sgbh {} for update", record["sgbh"].toString());
                    var accidentFileTemp = AccidentFile()
                    accidentFileTemp.sgbh = record["sgbh"].toString()
                    var accidentFile = accidentFileMapper.selectOne(accidentFileTemp)
                    jdbcTemplate.update(deleteSql, record["sgbh"].toString());

                    log.info("accident_file->insert sgbh {} after deletion", record["sgbh"].toString());
                    record.put("kskcsj", formatterTime(record["kskcsj"].toString()))
                    record.put("jskcsj", formatterTime(record["jskcsj"].toString()))
                    record.put("sgfssj", formatterTime(record["sgfssj"].toString()))
                    accidentFile.latitude?.let { record.put("latitude", it) }
                    accidentFile.longitude?.let { record.put("longitude", it) }
                    insertTemplate.execute(record)
                }
            }
        })

        records.clear()

        return 0
    }

    //    @Throws(SQLException::class)
    private fun getColumns(resultSet: ResultSet): Set<String> {
        val columns = Sets.newHashSet<String>()
        val metaData = resultSet.metaData
        for (i in 1 until metaData.columnCount) columns.add(metaData.getColumnLabel(i).toLowerCase())

        return columns
    }

    private fun formatterTime(times: String): String {
        var timeStringTemp = times


        if (timeStringTemp == null || timeStringTemp.isEmpty() || timeStringTemp.length < 10) {
            return "0000-00-00"
        } else {
            val timeTemp = Timestamp.valueOf(timeStringTemp)
            timeStringTemp = timeTemp.toLocalDateTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
        }
        return timeStringTemp
    }


    private fun trimValue(value: String): String {
        var trimmedValue = value
        try {
            trimmedValue = java.lang.Long.parseUnsignedLong(value).toString()
        } catch (e: RuntimeException) {

        }

        return if (trimmedValue.length > 32) trimmedValue.substring(0, 32) else trimmedValue
    }

    fun getConnection(): Connection? {
        var conn: Connection? = null
        val DRIVER = paramConfig.oracleSource.driverClassName
        val url = paramConfig.oracleSource.url
        val user = paramConfig.oracleSource.username
        val password = paramConfig.oracleSource.password
        try {
            Class.forName(DRIVER)
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        }

        try {
            conn = DriverManager.getConnection(url, user, password)
        } catch (e: SQLException) {
            e.printStackTrace()
        }

        return conn
    }

    companion object {
        private val log: Logger = LoggerFactory.getLogger(this::class.java)
    }
}